<?php

class Admincp_Model_Tour extends Zend_Db_Table_Abstract
{
    protected $_db;

    public function __construct()
    {
        $this->_db = Zend_Registry::get('db');
    }

    public function listTour($locale)
    {
        $sql = $this->_db->select()
                        ->from('tour AS t')
                        ->join('tour_language AS tl', 't.id = tl.tour_id', array('name','start_date'))
                        ->join('language AS l', 'tl.language_id = l.id', null)
                        ->where('l.locale = ?', $locale)
                        ->order('t.id DESC');
        return $sql;
    }

    public function getTourById($id)
    {
        $sql = $this->_db->select()
                        ->from('tour')
                        ->where('id = ?', $id);
        $result = $this->_db->fetchRow($sql);
        return $result;
    }
    
    public function getTourLanguageById($id)
    {
        $sql = $this->_db->select()
                        ->from('tour_language AS tl')
                        ->join('language AS l', 'tl.language_id = l.id', array('locale'))
                        ->where('tl.tour_id = ?', $id);
        $result = $this->_db->fetchAll($sql);
        return $result;
    }
    
    public function getImageById($id)
    {
        $sql = $this->_db->select()
                        ->from('tour', array('image'))
                        ->where('id = ?', $id);
        $result = $this->_db->fetchOne($sql);
        return $result;
    }
    
    public function checkTour($name, $id = null)
    {
        $sql = $this->_db->select()
                        ->from('tour_language')
                        ->where('name = ?', $name);
        if($id != null) {
            $sql = $sql->where('tour_id <> ?', $id);
        }
        $result = $this->_db->fetchAll($sql);
        return count($result);
    }
    
    public function addTour($data)
    {
        $this->_db->insert('tour',$data);
        return $this->_db->lastInsertId();
    }
    
    public function addTourLanguage($data)
    {
        $this->_db->insert('tour_language',$data);
    }
    
    public function deleteTour($id)
    {
        $id = (int) $id;
	    $where = $this->getAdapter()->quoteInto('id = ?', $id);
        $image = $this->getImageById($id);
        if(is_file(PUBLIC_PATH . '/files/tour/' . $image)) {
            @unlink(PUBLIC_PATH . '/files/tour/' . $image);
        }
        $this->_db->delete('tour', $where);
        $where = $this->getAdapter()->quoteInto('tour_id = ?', $id);
        $this->_db->delete('tour_language', $where);
    }
    
    public function deleteMultiTour($listId)
    {
        foreach($listId as $id) {
            $this->deleteTour($id);
        }
    }
    
    public function updateTour($data, $id)
    {
        $where = $this->getAdapter()->quoteInto('id = ?', $id);
        $this->_db->update('tour', $data, $where);
    }
    
    public function updateTourLanguage($data,$id)
    {
        $where = 'tour_id = '.$id.' AND language_id = '.$data['language_id'];
        $this->_db->update('tour_language', $data, $where);
    }
    
    public function searchTour($data, $locale)
    {
        $sql = $this->_db->select()
	                    ->from('tour AS t')
                        ->join('tour_language AS tl', 't.id = tl.tour_id', array('name','start_date'))
                        ->join('language AS l', 'tl.language_id = l.id', null)
                        ->where('l.locale = ?', $locale);
	    if($data['name'] != '') {
	        $sql = $sql->where('tl.name_plaintext LIKE ?', $data['name']);
	    }
	    if($data['status'] != 0) {
	        $sql = $sql->where('t.status = ?', $data['status']);
	    }
	    return $sql;
    }
}
